__author__ = 'dracher'
try:
    import xlrd
except ImportError as e:
    print "%s" % e
    exit(-1)
from xlwt import Workbook
import os
import re
import sqlite3

from public_parameters import USEFUL_COLUMN_INDEX, DATA_START_FLAG, \
    ALLOWED_ACTION_NAME, HTTP_RETURN_CODE, VALID_HEAD_COLUMN_NAME, TEST_MATERIAL_PATH, \
    TEST_RESULT_PATH, CASE_NAME_PREFIX

class QueryExcelError(Exception):
    """
    Base Exception of Query Excel Error
    """

class NoExcelData(QueryExcelError):
    """
    Can't get raw data to perform test
    """

class TestDrivenData(object):
    def __init__(self, excelFilePath):
        self.excelFilePath = excelFilePath
        #self.sheet_name = sheet_name
        self.sheet = None
        self.query_range_min = 0
        self.query_range_max = 0
        self.load_excel()
        self.get_row_range()

    def load_excel(self):
        if os.path.exists(self.excelFilePath):
            try:
                self.sheet = xlrd.open_workbook(self.excelFilePath).sheet_by_index(0)
            except Exception as e:
                raise e
        else:
            raise IOError, "%s doesn't exist" % self.excelFilePath

    def get_test_data(self, onlyTreeList=None):
        """
        """
        treeIndex = []
        treeCursor = [0]

        rowCollection = []
        for x in xrange(50) : rowCollection.append([])

        if not onlyTreeList:
            for i in xrange(self.query_range_min, self.query_range_max):
                if self.sheet.row_values(i)[6] == DATA_START_FLAG:
                    if self.sheet.row_values(i)[4] in treeIndex:
                        treeCursor[0] = treeIndex.index(self.sheet.row_values(i)[4])
                    else:
                        treeIndex.append(self.sheet.row_values(i)[4])
                        treeCursor[0] = treeIndex.index(self.sheet.row_values(i)[4])
                else:
                    rowCollection[treeCursor[0]].append([self.sheet.row_values(i)[k] for k in USEFUL_COLUMN_INDEX])

            return dict(zip(treeIndex, rowCollection[:len(treeIndex)]))
        else:
            for i in xrange(self.query_range_min, self.query_range_max):
                if self.sheet.row_values(i)[6] == DATA_START_FLAG:
                    if self.sheet.row_values(i)[4] in treeIndex:
                        pass
                    else:
                        treeIndex.append(self.sheet.row_values(i)[4])
            return treeIndex

    def get_row_range(self):
        """get range of rows of real data in excel file"""
        self.query_range_max = self.sheet.nrows
        for i in range(0, self.query_range_max):
            if self.sheet.row_values(i)[6] == DATA_START_FLAG:
                self.query_range_min = i
                break

    @property
    def get_case_count(self):
        return self.query_range_max - self.query_range_min

class DataValidation(TestDrivenData):
    def __init__(self, *args):
        TestDrivenData.__init__(self, *args)

    def verify_data(self):

        resDict = {}
        treeName = ""
        resLog = lambda row, column, right, wrong : ("row%s_column%s" % (row, column),
                                                     "should be === %s ===, not <<< %s >>>" % (right, wrong))
        comp = lambda aName, aList : [aName.upper()==x for x in aList]
        findNum = lambda str : re.findall(r'\d{3}|\d{2}', str)
        findAscii = lambda str : re.findall(r'[a-zA-Z]+', str)

        for i in xrange(self.query_range_min, self.query_range_max):
            if self.sheet.row_values(i)[6] == DATA_START_FLAG:
#                if not self.sheet.row_values(i)[4].endswith("xml"):
#                    treeName = self.sheet.row_values(i)[4]
#                    resDict[resLog(i, 4, "xxxx.xml", "xxxx")[0]] = resLog(i, 4, "xxxx.xml", "xxxx")[1]
#                else:
#                    treeName = self.sheet.row_values(i)[4].split(".")[0]
                treeName = self.sheet.row_values(i)[4]
                if self.sheet.row_values(i)[5] != VALID_HEAD_COLUMN_NAME[0] : \
                    resDict[resLog(i, 5, VALID_HEAD_COLUMN_NAME[0], self.sheet.row_values(i)[5])[0]] = resLog(i, 5, VALID_HEAD_COLUMN_NAME[0], self.sheet.row_values(i)[5])[1]
                if self.sheet.row_values(i)[7] != VALID_HEAD_COLUMN_NAME[2] : \
                    resDict[resLog(i, 7, VALID_HEAD_COLUMN_NAME[2], self.sheet.row_values(i)[7])[0]] = resLog(i, 7, VALID_HEAD_COLUMN_NAME[2], self.sheet.row_values(i)[7])[1]
                if self.sheet.row_values(i)[8] != VALID_HEAD_COLUMN_NAME[3] : \
                    resDict[resLog(i, 8, VALID_HEAD_COLUMN_NAME[3], self.sheet.row_values(i)[8])[0]] = resLog(i, 8, VALID_HEAD_COLUMN_NAME[3], self.sheet.row_values(i)[8])[1]
            else:
                if True not in comp(self.sheet.row_values(i)[4].upper().strip(), ALLOWED_ACTION_NAME):
                    t = resLog(i, 4, str(ALLOWED_ACTION_NAME), self.sheet.row_values(i)[4])
                    resDict[t[0]] = t[1]
                if not findNum(self.sheet.row_values(i)[6]):
                    t = resLog(i, 6, "contain at least one of %s" % HTTP_RETURN_CODE, "Nothing")
                    resDict[t[0]] = t[1]
                try:
                    if self.sheet.row_values(i)[2].upper() != treeName.split('/')[-1].split(".")[0].upper():
                        t = resLog(i, 2, treeName.split('/')[-1], self.sheet.row_values(i)[2])
                        resDict[t[0]] = t[1]
                except AttributeError:
                    if str(int(self.sheet.row_values(i)[2])).upper() != treeName.split('/')[-1].split(".")[0].upper():
                        t = resLog(i, 2, treeName.split('/')[-1], self.sheet.row_values(i)[2])
                        resDict[t[0]] = t[1]
                if self.sheet.row_values(i)[4].upper() == "DELETE":
                    if findAscii(str(self.sheet.row_values(i)[8])):
                        t = resLog(i, 8, "numbers", str(self.sheet.row_values(i)[8]))
                        resDict[t[0]] = t[1]
                if self.sheet.row_values(i)[4].upper() == "GET":
                    if self.sheet.row_values(i)[7] == "":
                        t = resLog(i, 7, "a real file path", "empty")
                        resDict[t[0]] = t[1]
                if self.sheet.row_values(i)[4].upper() == "PUT" or \
                   self.sheet.row_values(i)[4].upper() == "POST":
                    if self.sheet.row_values(i)[8] == "":
                        t = resLog(i, 7, "a real file path", "empty")
                        resDict[t[0]] = t[1]
        return resDict

class WriteBackResultToXls(object):
    def __init__(self, dbName, excelFiles):
        self.dbName = dbName
        self.excelFiles = excelFiles
        self.dataFromDB = self.__fetch_data_from_db()

    def __fetch_data_from_db(self):
        con = sqlite3.connect(os.path.join(TEST_RESULT_PATH, self.dbName))
        cur = con.cursor()
        cur.execute("select case_name2, case_result from records")
        caseDetails = cur.fetchall()
        cur.execute("select * from info")
        printerDetails = cur.fetchall()
        cur.close()
        con.close()
        return caseDetails, printerDetails

    def write_back(self):
        book = Workbook()
        sheet1 = book.add_sheet('Sheet 1', cell_overwrite_ok=True)
        for i, item in enumerate(self.dataFromDB[1][0][1:5]):
            sheet1.write(0, i, item)

        for k, v in enumerate(self.dataFromDB[0]):
            sheet1.write(k+1, 0, v[0])
            if v[1] == 1:
                sheet1.write(k+1, 1, 'Pass')
            else:
                sheet1.write(k+1, 1, 'Fail')

        book.save(os.path.join(TEST_RESULT_PATH, self.dbName.split('.')[0] + '.xls',))

    def __get_test_pattern(self, name):
        pattern = None
        for i in CASE_NAME_PREFIX:
            if i in name : pattern = i
        return pattern and pattern or "Something wrong about CaseName"

    def forTest(self):
        pass

if __name__ == "__main__":
    q = TestDrivenData(excelFilePath='/Volumes/Backup/My_Project/LEDMTools/testMaterial/Forrester_LEDM_Test_Fax_Updated_2012_10_29.xls')

    print q.get_test_data()
